package org.jin.samples.utils;

import java.io.InputStream;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jin.samples.model.vo.ExamUserVO;

public class ExcelUtils {
	public static final String OFFICE_EXCEL_2003_POSTFIX = ".xls";
	public static final String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";
			
	public static Workbook openWorkbook(InputStream is,String postfix){
		Workbook hssfWorkbook = null;
		try{
			if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
				hssfWorkbook = new HSSFWorkbook(is);  //创建2003版本的对象
			}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
				hssfWorkbook = new XSSFWorkbook(is);  //创建2007版本的对象
			}
		} catch(Exception e){
			if(e instanceof NotOLE2FileException){
				return null;
			}
			e.printStackTrace();
		}
		return hssfWorkbook;
	}
	
	
	public static List<ExamUserVO> getStudentListByExcel(Workbook wk){
		final String GENDER_MALE_STRING = "男", GENDER_FEMALE_STRING = "女";
		final Integer GENDER_MALE_INTEGER = 0, GENDER_FEMALE_INTEGER = 1;
		List<ExamUserVO> resultList = new ArrayList<ExamUserVO>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNubmer = 0;
		int maxRowNumber = sheet.getLastRowNum();
		ExamUserVO vo = null;
		for(int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber ++){
			Row row = sheet.getRow(currentRowNumber);
			maxCellNubmer = row.getLastCellNum();
			vo = new ExamUserVO();
			for(int currentCellNumber = 0; currentCellNumber <= maxCellNubmer; currentCellNumber ++){
				Cell cell = row.getCell(currentCellNumber);
				switch(currentCellNumber){
				case 0:
					//获取学号列数据
					vo.setUserId((String)getCellValue(cell));
					break;
				case 1:
					//姓名
					vo.setUserName((String) getCellValue(cell));
					break;
				case 2: 
					//性别
					String gender = (String)getCellValue(cell);
					if(GENDER_MALE_STRING.equals(gender)){
						vo.setGender(GENDER_MALE_INTEGER);//男
					}else if(GENDER_FEMALE_STRING.equals(gender)){
						vo.setGender(GENDER_FEMALE_INTEGER);//女
					}
					break;
				case 3:
					//获取联系电话列数据
					vo.setTel((String) getCellValue(cell));
					break;
				case 4:
					//获取邮箱
					vo.setEmail((String) getCellValue(cell));
					break;
				case 5:
					//获取地址
					vo.setAddress((String)getCellValue(cell));
					break;
				case 6:
					//获取生日
					vo.setBirthday(getCellValue(cell));
					break;
				case 7:
					//获取密码
					String decodingPassword = ((String) getCellValue(cell));
					try {
						vo.setPassword(MD5Utils.GetMD5Code(decodingPassword));
					} catch (NoSuchAlgorithmException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					break;
				case 8:
					//获取最后一次登录时间
					Date date = null;
					if(HSSFDateUtil.isCellDateFormatted(cell)){
						date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
						System.out.println(date);
					}
					vo.setLastLockTime(date);
					break;
				case 9:
					//获取失败次数
					vo.setLoginFailCount(Integer.valueOf(getCellValue(cell)));
					break;
				}
			}
			resultList.add(vo);
			}
			return resultList;
		}
	
	@SuppressWarnings("static-access")
	public static String getCellValue(Cell cell){
		if(cell == null){
			return "";
		}
		if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
			DecimalFormat df = new DecimalFormat("0");//取一位整数
			return df.format(cell.getNumericCellValue());
		}else {
			return String.valueOf(cell.getStringCellValue());
		}
	}
	
	}
	
	
